package product;



import java.awt.BasicStroke;
import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Font;
import java.awt.GradientPaint;
import java.awt.Paint;
import java.io.FileInputStream;
import java.io.ObjectInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;
import java.util.Vector;

import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;

import org.jfree.chart.ChartFrame;
import org.jfree.chart.ChartPanel;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.axis.AxisLocation;
import org.jfree.chart.axis.CategoryAxis;
import org.jfree.chart.axis.CategoryLabelPositions;
import org.jfree.chart.axis.NumberAxis;
import org.jfree.chart.labels.CategoryItemLabelGenerator;
import org.jfree.chart.labels.ItemLabelAnchor;
import org.jfree.chart.labels.ItemLabelPosition;
import org.jfree.chart.labels.StandardCategoryItemLabelGenerator;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.plot.DatasetRenderingOrder;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.renderer.category.BarRenderer;
import org.jfree.chart.renderer.category.CategoryItemRenderer;
import org.jfree.chart.renderer.category.LineAndShapeRenderer;
import org.jfree.chart.renderer.category.StandardBarPainter;
import org.jfree.chart.title.TextTitle;
import org.jfree.data.RangeType;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.ui.GradientPaintTransformType;
import org.jfree.ui.HorizontalAlignment;
import org.jfree.ui.StandardGradientPaintTransformer;
import org.jfree.ui.TextAnchor;

public class PolylineBarChart {

	Vector sal = new Vector<>();
	Vector chul = new Vector<>();
	Vector hol = new Vector<>();
	
	Connection conn;
	String yy; // 연도
	int qq; // 분기
	String person;  // 사번
	String a1,a2,a3; // SQL문 입력값
	int min; int max;	//최소 , 최대값
	int allnum=0;

	public PolylineBarChart(Connection conn,String y,String q,String p) {
		this.conn=conn;
		this.yy =y;
		this.qq = Integer.parseInt(q);
		this.person = p;
	}
	
	// 사원급여 - 분기별
	public void salquery(){
		String sql;
		if(qq==1){
			a1 = "s_01" ; a2 = "s_02"; a3 = "s_03";
		} else if(qq==2){
			a1 = "s_04" ; a2 = "s_05"; a3 = "s_06";
		} else if(qq==3){
			a1 = "s_07" ; a2 = "s_08"; a3 = "s_09";
		} else {
			a1 = "s_10" ; a2 = "s_11"; a3 = "s_12";
		}
			sql = "select "+a1+","+a2+","+a3+" "
					+ "from salary where memberid=? and substr(s_year,1,2) =?";
			String a1 = person;
			String a2 = yy;
		try{
			PreparedStatement pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, a1);
			pstmt.setString(2, a2);
			ResultSet rs = pstmt.executeQuery();
			//컬럼명을 테이블에 세팅
			ResultSetMetaData rsm = rs.getMetaData();
			// 데이터 추가
			if(rs.next()){
				for(int i=1; i<=rsm.getColumnCount();i++){
					sal.add(rs.getString(i));
				}
			}else {
				for(int i=1; i<=rsm.getColumnCount();i++){
					sal.add("0");
				}
			}
			rs.close();
			pstmt.close();
		}
		catch(Exception ex){
		}
	}
	
	// 사원출석 - 분기별
	public void chulquery(){

		if(qq==1){
			a1 = yy+"/01"; a2 = yy+"/02"; a3 = yy+"/03";
		} else if(qq==2){
			a1 = yy+"/04"; a2 = yy+"/05"; a3 = yy+"/06";
		} else if(qq==3){
			a1 = yy+"/07"; a2 = yy+"/08"; a3 = yy+"/09";
		} else {
			a1 = yy+"/10"; a2 = yy+"/11"; a3 = yy+"/12";
		}		
		
		String sql = "select (select count(chuldate) from chuldata where memberid='"+person+"' "
				+ "and substr(chuldate,1,5) ='"+a1+"' and chultype=1),"
				+ "(select count(chuldate) from chuldata where memberid='"+person+"' "
				+ "and substr(chuldate,1,5) ='"+a2+"' and chultype=1),"
				+ "(select count(chuldate) from chuldata where memberid='"+person+"' "
				+ "and substr(chuldate,1,5) ='"+a3+"' and chultype=1)"
				+ "from dual";
		try{
			PreparedStatement pstmt = this.conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			//컬럼명을 테이블에 세팅
			ResultSetMetaData rsm = rs.getMetaData();
			// 데이터 추가
			if(rs.next()){
				for(int i=1; i<=rsm.getColumnCount();i++){
					chul.add(rs.getString(i));
				}
			}else{
				for(int i=1; i<=rsm.getColumnCount();i++){
					chul.add("0");
				}
			}
			rs.close();
			pstmt.close();
		}
		catch(Exception ex){
		}
	}
	
	// 사원급여 - 연간
	public void totsal(){
		
		String sql;
			sql = "select * from salary where memberid=? and substr(s_year,1,2) =?";
			String a1 = person;
			String a2 = yy;
		try{
			PreparedStatement pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, a1);
			pstmt.setString(2, a2);
			ResultSet rs = pstmt.executeQuery();
			ResultSetMetaData rsm = rs.getMetaData();
			// 데이터 추가
			if(rs.next()){
				for(int i=4; i<=rsm.getColumnCount();i++){
					sal.add(rs.getString(i));
				}
			}else {
				for(int i=4; i<=rsm.getColumnCount();i++){
					sal.add("0");
				}
			}
			rs.close();
			pstmt.close();
		}
		catch(Exception ex){
		}
	}
	
	// 사원출석 - 연간
	public void totchul(){

		for(int m=1;m<=12;m++){
			if (m<10) a1 = yy+"/0"+m;  else a1 = yy+"/"+m;
			
		String sql = "select(select count(chuldate) from chuldata "
				+ "where memberid='"+person+"' and substr(chuldate,1,5)='"+a1+"') "
				+ "from dual";
		try{
			PreparedStatement pstmt = this.conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			//컬럼명을 테이블에 세팅
			ResultSetMetaData rsm = rs.getMetaData();

			if(rs.next()){
				for(int i=1; i<=rsm.getColumnCount();i++){
					chul.add(rs.getString(i));
				}
			}else{
				for(int i=1; i<=rsm.getColumnCount();i++){
					chul.add("0");
				}
			}
			rs.close();
			pstmt.close();
		}
		catch(Exception ex){  }
		}
	}
	
	public JFreeChart getChart2() {
		if(qq==5) {
			totsal();	totchul();
		}
		else {
			salquery();	chulquery();
		}
		DefaultCategoryDataset dataset1 = new DefaultCategoryDataset();                // bar chart 1
		DefaultCategoryDataset dataset3 = new DefaultCategoryDataset();                // line chart 1

		String[] name = {"출석일","급여"};

		// 데이터 입력 ( 값, 범례, 카테고리 )
		if(qq==1){
			min=1; max=3;
		}else if (qq==2){
			min=4; max=6;
		}else if (qq==3){
			min=7; max=9;
		}else if (qq==4){
			min=10; max=12;
		}else{
			min=1; max=12;
		}
			// 실제 차트 데이터 출력
			
			int z=-1;	
			for (int y=min; y<=max; y++){
				z++;
				dataset3.addValue(Integer.parseInt((String) chul.get(z)), name[0], y+"월");
				dataset1.addValue(Integer.parseInt((String) sal.get(z))/10000, name[1], y+"월");
				allnum =allnum+ Integer.parseInt((String) chul.get(z)) +Integer.parseInt((String) sal.get(z));
			}
			
			// 벡터의 모든 값을 더하여 0이면 데이터 없으므로 임의로 넣어줌
			if (allnum==0) {
				z=-1;	
				
				for (int y=min; y<=max; y++){
					z++;
					dataset3.setValue(10,  name[0], y+"월");
					dataset1.setValue(100, name[1], y+"월");
				}
			}
			
		sal.removeAllElements();
		chul.removeAllElements();
		hol.removeAllElements();
		
	    // 렌더링 생성 및 세팅
        // 렌더링 생성
        final BarRenderer renderer1 = new BarRenderer();
        final LineAndShapeRenderer renderer3 = new LineAndShapeRenderer();
        
        // 공통 옵션 정의
        final CategoryItemLabelGenerator generator = new StandardCategoryItemLabelGenerator();
        final ItemLabelPosition p_center = 
        		new ItemLabelPosition(ItemLabelAnchor.CENTER, TextAnchor.CENTER );
        final ItemLabelPosition p_below = 
        		new ItemLabelPosition(ItemLabelAnchor.OUTSIDE6, TextAnchor.TOP_LEFT);
 

        Font f = new Font("Gulim", Font.BOLD, 14);
        Font axisF = new Font("휴먼옛체", Font.PLAIN, 14);
        Font axisF2 = new Font("Gothic", Font.BOLD, 16);
        
        

        // 그래프 1
        renderer1.setBaseItemLabelGenerator(generator);
        renderer1.setBasePositiveItemLabelPosition(p_center);
        renderer1.setBaseItemLabelFont(f);
        renderer1.setGradientPaintTransformer(new StandardGradientPaintTransformer(
                GradientPaintTransformType.VERTICAL));
        renderer1.setMaximumBarWidth(0.13); 
        renderer1.setBaseItemLabelsVisible(true);  // 라벨값 보이기(차트가 좁으면 안보임) //<수정>
        
        
        // 그래프 3        
        renderer3.setBaseItemLabelGenerator(generator);
        renderer3.setBaseItemLabelsVisible(false);//<수정>
        // 꺽은선 그래프 포인트 강조 true/false
        
        renderer3.setDrawOutlines(true);	// 포인트 네모모양
        renderer3.setUseFillPaint(true);	// 포인트 색
        renderer3.setBaseFillPaint(Color.WHITE); 
        renderer3.setBaseItemLabelFont(f);
        renderer3.setBasePositiveItemLabelPosition(p_below);
//        renderer3.setSeriesPaint(0,new Color(219,121,22));
        renderer3.setSeriesStroke
        	(0,new BasicStroke(2.0f,BasicStroke.CAP_ROUND, BasicStroke.JOIN_ROUND,3.0f));
        



        // plot 생성
        final CategoryPlot plot = new CategoryPlot();

        // plot 에 데이터 적재
        plot.setDataset(dataset1);
        plot.setRenderer(renderer1);
        plot.setDataset(2, dataset3);
        plot.setRenderer(2, renderer3);

        // plot 기본 설정
        plot.setOrientation(PlotOrientation.VERTICAL);             // 그래프 표시 방향
		plot.setRangeGridlinePaint(Color.red);						// 가이드라인 컬러
        plot.setRangeGridlinesVisible(true);                       // X축 가이드 라인 표시여부 <false>
        plot.setDomainGridlinePaint(Color.red);						// 가이드라인 컬러
        plot.setDomainGridlinesVisible(false);                      // Y축 가이드 라인 표시여부

        // 렌더링 순서 정의 : dataset 등록 순서대로 렌더링 ( 즉, 먼저 등록한게 아래로 깔림 )
        plot.setDatasetRenderingOrder(DatasetRenderingOrder.FORWARD);

        // X축 세팅
        plot.setDomainAxis(new CategoryAxis());              // X축 종류 설정
        plot.getDomainAxis().setTickLabelFont(f); 		// X축 눈금라벨 폰트 조정

        // Y축 세팅
        NumberAxis Axis = new NumberAxis("급여");
        plot.setRangeAxis(Axis);                // Y축 종류 설정
        
        Axis.setLabelFont(axisF2);
        Axis.setTickLabelFont(axisF);  // Y축 눈금라벨 폰트 조정
        Axis.setLabelPaint(Color.blue);
        Axis.setTickLabelPaint(Color.blue);
//      Axis.setLabelAngle( 90 * (Math.PI / 180.0) );
        Axis.setRangeType(RangeType.POSITIVE); // 값이 '0'일때  Y 축에서의 위치 ( 상(RangeType.NEGATIVE), 중(설정안함), 하(RangeType.POSITIVE))

        
        //Y축 추가함
        NumberAxis Axis1 = new NumberAxis("출근일");
        plot.setRangeAxis(2,Axis1);
        
        Axis1.setLabelFont(axisF2);
        Axis1.setTickLabelFont(axisF);
        Axis1.setLabelPaint(Color.red);
        Axis1.setTickLabelPaint(Color.red);
//        Axis1.setLabelAngle( 270 * (Math.PI / 180.0) );  // 270은 돌릴려는 각도
        Axis1.setRangeType(RangeType.POSITIVE); 

        if( allnum ==0){
	        plot.setRangeGridlinesVisible(false);
	        Axis.setAxisLineVisible(false);
	        Axis1.setAxisLineVisible(false);
	        renderer1.setSeriesPaint(0, Color.WHITE);
	        renderer1.setShadowVisible(false); // 그림자 지우기
	        renderer3.setSeriesPaint(0, Color.WHITE);
	        renderer1.setBaseItemLabelsVisible(false);  // 라벨값 보이기(차트가 좁으면 안보임) //<수정>
	        renderer3.setBaseItemLabelsVisible(false);

        } else {
			renderer1.setSeriesPaint(0, new Color(0,162,255));
        }
        
        
        plot.getDomainAxis().setCategoryLabelPositions(CategoryLabelPositions.UP_45);  
//      plot.getDomainAxis().setCategoryLabelPositions(CategoryLabelPositions.STANDARD);       // 카테고리 라벨 위치 조정
        plot.setRangeAxisLocation(1, AxisLocation.BOTTOM_OR_RIGHT);
        
        // Y축에 데이터 맞춤
        plot.setDataset(2, dataset3);	// 두번째 축에 데이타 설정
        plot.mapDatasetToRangeAxis(2, 2); // 두번째 축에 두번째 데이타 매핑


        // 세팅된 plot을 바탕으로 chart 생성
        TextTitle main;
        final JFreeChart chart = new JFreeChart(plot);
        if (qq==5){
        	main = new TextTitle(yy+"년 전체 급여/출근일", new Font("휴먼엑스포", Font.PLAIN, 18));
        }else {
        	main = new TextTitle(yy+"년 "+qq+"분기 급여/출근일", new Font("휴먼엑스포", Font.PLAIN, 18));
        }

        chart.setTitle(main); // 차트 타이틀
        TextTitle copyright = new TextTitle("  단위 : (만)원", new Font("SansSerif", Font.PLAIN, 14));
        copyright.setHorizontalAlignment(HorizontalAlignment.LEFT);
        chart.addSubtitle(copyright);  // 차트 서브 타이틀
        return chart;
        
    }
}


